import pymssql
import sys
import GenLog
import time

class DbUtil(object):
    def __init__(self, host=None, user=None, password=None, db_name=None):
        # 初始化主机用户名密码和数据库名
        self.host = host
        self.user = user
        self.password = password
        self.db_name = db_name

        # 初始化链接游标和受影响行数
        self._conn = None
        self._cursor = None
        self.affected_rows = 0

    '''定义连接'''
    def get_conn(self):
        # 判断连接时主机用户名密码和数据库名是否为空，若为空直接中断0
        if self.host is None or self.user is None or self.password is None or self.db_name is None:
            filestr = 'Fatal error,can not get database connection.host,user,password or db_name does not specified.'
            GenLog.GenSQLog(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + '\n' + filestr)
            sys.exit(0)
        # 获取连接和游标时，捕获异常
        try:
            self._conn = pymssql.connect(self.host, self.user, self.password, self.db_name, autocommit=False)
            self._cursor = self._conn.cursor(as_dict=True)
        except Exception as e:
            filestr = 'Error while get connection to database, error message{}'.format(e)
            GenLog.GenSQLog(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + '\n' + filestr)
        if self._conn is None:
            filestr = 'can not connect to database {} on server {}'.format(self.db_name, self.host)
            GenLog.GenSQLog(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + '\n' + filestr)
            sys.exit(-100)

    '''定义查询'''
    def query(self, query: str):
        if self._conn is None:
            self.get_conn()
        try:
            self._cursor.execute(query)
            return self._cursor.fetchall(), self._cursor.rowcount
        except Exception as e:
            print('Execute query failed while exception message:{}'.format(str(e)))
        return 0, None

    '''定义执行'''
    def execute(self, excute: str, bind_vars: tuple = None):   # query:str表示建议query参数为字符串
        # 首先匹配连接是否存在，不存在提示‘没有数据库存在，尝试重新连接’，并重新调用连接方法
        if self._conn is None:
            filestr = 'No Db connection exists, try to get connection to database.'
            GenLog.GenSQLog(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + '\n' + filestr)
            self.get_conn()
        # 执行SQL语句，获取受影响行数，执行提交，捕获异常，若有异常提示失败的操作异常并返回-1
        try:
            # 执行之前提示你输入的SQL语句以及绑定的元组
            filestr = 'Run execute SQL:{} \n'.format(excute)
            GenLog.GenSQLog(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + '\n' + filestr)
            self._cursor.execute(excute)
            self.affected_rows = self._cursor.rowcount
            self._conn.commit()
            # 执行之后提示受影响的行数以及输出‘执行成功’
            filestr = 'excute affected rows:{}\n'.format(self.affected_rows)
            GenLog.GenSQLog(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + '\n' + filestr)
            return 0
        except Exception as e:
            filestr = 'run execute SQL failed while exception message: {}\n'.format(str(e))
            GenLog.GenSQLog(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + '\n' + filestr)
            return -1

    '''回收连接'''
    def __del__(self):
        if self._conn is not None:
            self._conn.close()

